using Mono.Data.SqliteClient;
using System;
using System.Data;
using System.Collections;
using System.IO;
using System.Xml;

namespace Tomboy
{
	
	public class TomboyDataProviderSQLite : Tomboy.TomboyDataProvider
	{
		
		private SqliteConnection cnn=null;
		private bool gconfCreated= false; 

		static private TomboyDataProvider provider = null;
		string nomeFile = "";
		private System.IO.MemoryStream mstream=null;
		private System.IO.MemoryStream gconfStream=null;

		public TomboyDataProviderSQLite()
		{
			cnn = new SqliteConnection("URI=file:tomboy.db,version=2");
			cnn.Open();
			
			if (provider==null) {
				provider = this;
			}

//			CreateDirectory(null);
		}
		
		public bool DirectoryExist(string directory) {
			// Chech if the tomboy table is present
			// At this moment the directory data is not used 
//			IDbCommand dbcmd=null;
			System.Data.Common.DbCommand dbcmd=null;
//			SqliteCommand dbcmd=null;
			string sql;
			bool ret=false;
//			IDataReader dr=null;
//			SqliteDataReader dr=null;
			System.Data.Common.DbDataReader dr=null;

			// Try to create insert data into the Table
			try {
				sql="SELECT count(*) as nrec FROM tomboy_notes";
				dbcmd = cnn.CreateCommand();
				dbcmd.CommandText = sql;
				dr=dbcmd.ExecuteReader();

				if (dr.Read()) {
					ret=true;
				}
				
			} catch {
			} finally {
				try { dr.Close(); } catch (Exception ex) { Console.WriteLine("Exception closing the Data Reader with the message: " + ex.Message); }
				try { dbcmd.Dispose(); } catch (Exception ex) { Console.WriteLine("Exceptio closing the command database with the message: " + ex.Message); }
			}
			
			return ret;
		}
		
		public DirectoryInfo CreateDirectory (string directory)
		{
			// I suppose to create the table to contains the notes.
//			IDbCommand dbcmd=null;
			System.Data.Common.DbCommand dbcmd=null;
//			SqliteCommand dbcmd=null;
			string sql;

			// Try to create DB new Table
			try {
				sql = "CREATE TABLE tomboy_notes ("
				    + "  filename TEXT "
				    + " ,note TEXT "
				    + ")";
				dbcmd = cnn.CreateCommand();
				dbcmd.CommandText = sql;
				dbcmd.ExecuteNonQuery();
			} catch (Exception ex) {
				Console.WriteLine("Unable to create TABLE tombox_notes. " + ex.Message);
			} finally {
				dbcmd.Dispose();
			}
			
			return null;
		}

		public bool FileExists (string filePath)
		{
//			IDbCommand dbcmd=null;
			System.Data.Common.DbCommand dbcmd=null;
//			SqliteCommand dbcmd=null;
			string sql;
			bool ret=false;
//			IDataReader dr=null;
//			SqliteDataReader dr=null;
			System.Data.Common.DbDataReader dr=null;

			// Try to create insert data into the Table
			try {
				sql = "SELECT count(*) as nrec "
				    + "FROM tomboy_notes "
				    + "WHERE filename='" + filePath + "' "
				    ;
				dbcmd = cnn.CreateCommand();
				dbcmd.CommandText = sql;
				dr=dbcmd.ExecuteReader();

				if (dr.Read()) {
					ret=true;
				}
				
			} catch {
			} finally {
				try { dr.Close(); } catch (Exception ex) { Console.WriteLine("Exception closing the Data Reader with the message: " + ex.Message); }
				try { dbcmd.Dispose(); } catch (Exception ex) { Console.WriteLine("Exceptio closing the command database with the message: " + ex.Message); }
			}
			
			return ret;
		}

		public bool FileDelete (string filePath)
		{
//			IDbCommand dbcmd=null;
//			SqliteCommand dbcmd=null;
			System.Data.Common.DbCommand dbcmd=null;
			string sql;
			bool ret=false;
			int nRowModify=0;

			// Try to create insert data into the Table
			try {
				sql = "DELETE "
				    + "FROM tomboy_notes "
				    + "WHERE filename='" + filePath + "' "
				    ;
				dbcmd = cnn.CreateCommand();
				dbcmd.CommandText = sql;
				nRowModify=dbcmd.ExecuteNonQuery();

				if (nRowModify>0) {
					ret=true;
				}
			} catch {
			} finally {
				try { dbcmd.Dispose(); } catch (Exception ex) { Console.WriteLine("Exceptio closing the command database with the message: " + ex.Message); }
			}
			
			return ret;
		}

		public void FileMove (string fileSrc,string fileDst)
		{
//			IDbCommand dbcmd=null;
//			SqliteCommand dbcmd=null;
			System.Data.Common.DbCommand dbcmd=null;
			string sql;

			// Try to create insert data into the Table
			try {
				sql = "UPDATE tomboy_notes "
				    + "SET filename='" + fileDst + "' "
				    + "WHERE filename='" + fileSrc + "' "
				    ;
				dbcmd = cnn.CreateCommand();
				dbcmd.CommandText = sql;
				dbcmd.ExecuteNonQuery();

			} catch {
			} finally {
				try { dbcmd.Dispose(); } catch (Exception ex) { Console.WriteLine("Exceptio closing the command database with the message: " + ex.Message); }
			}
			
			return;
		}

		public string [] DirectoryGetFiles (string notes_dir, string pattern) {
//			IDbCommand dbcmd=null;
//			SqliteCommand dbcmd=null;
			System.Data.Common.DbCommand dbcmd=null;
			string sql;
			int i;
//			IDataReader dr=null;
//			SqliteDataReader dr=null;
			System.Data.Common.DbDataReader dr=null;
			ArrayList files=new ArrayList();

			// Try to create insert data into the Table
			try {
				sql = "SELECT filename "
				    + "FROM tomboy_notes "
				    + "WHERE filename like '" + pattern.Replace("*","%") + "' "
				    ;
				dbcmd = cnn.CreateCommand();
				dbcmd.CommandText = sql;
				dr = dbcmd.ExecuteReader();
				
				while (dr.Read()) {
					files.Add(dr.GetString(0));
				}

			} catch {
			} finally {
				try { dr.Close(); } catch (Exception ex) { Console.WriteLine("Exception closing the Data Reader with the message: " + ex.Message); }
				try { dbcmd.Dispose(); } catch (Exception ex) { Console.WriteLine("Exceptio closing the command database with the message: " + ex.Message); }
			}
			
			string [] list=new string[files.Count];
			for (i=0; i<files.Count; i++) 
			{
				list[i] = (string)files[i];
			}
			
			return list;
		}

		public XmlTextWriter getXmlTextWriter (string tmp_file, System.Text.Encoding encoding)
		{
		
			mstream = new System.IO.MemoryStream();
			XmlTextWriter xml = new XmlTextWriter (mstream, encoding);
			nomeFile = tmp_file;
			return xml;
		}
		
		public XmlTextReader getXmlTextReader (string tmp_file, System.Text.Encoding encoding)
		{
//			IDbCommand dbcmd=null;
//			SqliteCommand dbcmd=null;
			System.Data.Common.DbCommand dbcmd=null;
			string sql;
//			IDataReader dr=null;
//			SqliteDataReader dr=null;
			System.Data.Common.DbDataReader dr=null;
			string note="";
			mstream = new System.IO.MemoryStream();
			XmlTextReader xml = null;

			try {
				sql = "SELECT note FROM tomboy_notes WHERE filename='" + tmp_file + "' ";
				dbcmd = cnn.CreateCommand();
				dbcmd.CommandText = sql;
				
				dr = dbcmd.ExecuteReader();
				if (dr.Read()) {
					note = dr.GetString(0);
				}

				mstream = new System.IO.MemoryStream(encoding.GetBytes(note),false);
				xml = new XmlTextReader (mstream);
				
			} catch (Exception ex) {
				Console.WriteLine("Exception: " + ex.Message);
			} finally {
				try { dr.Close(); } catch (Exception ex) { Console.WriteLine("Exception closing the Data Reader with the message: " + ex.Message); }
				try { dbcmd.Dispose(); } catch (Exception ex) { Console.WriteLine("Exceptio closing the command database with the message: " + ex.Message); }
			}
					
			return xml;
		}
		
		public void textWriterClose (XmlTextWriter writer)
		{
//			IDbCommand dbcmd=null;
//			SqliteCommand dbcmd=null;
			System.Data.Common.DbCommand dbcmd=null;
			string sql;

			// Try to create insert data into the Table
			writer.Flush();
			
			mstream.Seek(0, SeekOrigin.Begin);
			StreamReader memoryReader = new StreamReader(mstream);

			try {
				sql = "INSERT INTO tomboy_notes "
				    + "( filename "
				    + " ,note "
				    + ") VALUES ( "
				    + "  ?"
				    + " ,? "
				    + ")";
				dbcmd = cnn.CreateCommand();
				dbcmd.CommandText = sql;
//				IDbDataParameter par1=dbcmd.CreateParameter();
//				SqliteParameter par1=dbcmd.CreateParameter();
				System.Data.Common.DbParameter par1=dbcmd.CreateParameter();
				par1.Value = nomeFile;
				dbcmd.Parameters.Add(par1);

//				IDbDataParameter par2=dbcmd.CreateParameter();
//				SqliteParameter par2=dbcmd.CreateParameter();
				System.Data.Common.DbParameter par2=dbcmd.CreateParameter();
				par2.Value = memoryReader.ReadToEnd();
				dbcmd.Parameters.Add(par2);
				
				dbcmd.ExecuteNonQuery();
			} catch (Exception ex) {
				Console.WriteLine("Exception: " + ex.Message);
			} finally {
				try { dbcmd.Dispose(); } catch (Exception ex) { Console.WriteLine("Exceptio closing the command database with the message: " + ex.Message); }
			}
		}
				
		public void xmlDocumentSave(XmlDocument document, String filename) {
			System.Data.Common.DbCommand dbcmd=null;
			string sql;

			// Try to create gconf table
			if (!gconfCreated) {

				// Try to create DB new Table
				try {
					sql = "CREATE TABLE tomboy_gconf ("
					    + " conf TEXT "
					    + ")";
					dbcmd = cnn.CreateCommand();
					dbcmd.CommandText = sql;
					dbcmd.ExecuteNonQuery();
				} catch (Exception ex) {
					Console.WriteLine("Unable to create TABLE tombox_notes. " + ex.Message);
				} finally {
					dbcmd.Dispose();
					gconfCreated=true;
				}
			}
			
			// Try to create insert data into the Table			
			gconfStream = new System.IO.MemoryStream();
			XmlTextWriter xml = new XmlTextWriter (gconfStream, System.Text.Encoding.UTF8);

			document.Save(xml);
			xml.Flush();
			
			gconfStream.Seek(0, SeekOrigin.Begin);
			StreamReader memoryReader = new StreamReader(gconfStream);

			try {
				sql = "INSERT INTO tomboy_gconf "
				    + "( conf "
				    + ") VALUES ( "
				    + "  ?"
				    + ")";
				dbcmd = cnn.CreateCommand();
				dbcmd.CommandText = sql;
//				IDbDataParameter par1=dbcmd.CreateParameter();
//				SqliteParameter par1=dbcmd.CreateParameter();
				System.Data.Common.DbParameter par1=dbcmd.CreateParameter();
				par1.Value = memoryReader.ReadToEnd();
				dbcmd.Parameters.Add(par1);
				
				dbcmd.ExecuteNonQuery();
			} catch (Exception ex) {
				Console.WriteLine("Exception: " + ex.Message);
			} finally {
				try { dbcmd.Dispose(); } catch (Exception ex) { Console.WriteLine("Exceptio closing the command database with the message: " + ex.Message); }
			}
		}
		
		public void XmlDocumentLoad(XmlDocument document, String filename) {
//			IDbCommand dbcmd=null;
//			SqliteCommand dbcmd=null;
			System.Data.Common.DbCommand dbcmd=null;
			string sql;
//			IDataReader dr=null;
//			SqliteDataReader dr=null;
			System.Data.Common.DbDataReader dr=null;
			string note="";
			mstream = new System.IO.MemoryStream();
			XmlTextReader xml = null;

			try {
				sql = "SELECT note FROM tomboy_gconf";
				dbcmd = cnn.CreateCommand();
				dbcmd.CommandText = sql;
				
				dr = dbcmd.ExecuteReader();
				if (dr.Read()) {
					note = dr.GetString(0);
				}

				mstream = new System.IO.MemoryStream(System.Text.Encoding.UTF8.GetBytes(note),false);
				xml = new XmlTextReader (mstream);
				
			} catch (Exception ex) {
				Console.WriteLine("Exception: " + ex.Message);
			} finally {
				try { dr.Close(); } catch (Exception ex) { Console.WriteLine("Exception closing the Data Reader with the message: " + ex.Message); }
				try { dbcmd.Dispose(); } catch (Exception ex) { Console.WriteLine("Exceptio closing the command database with the message: " + ex.Message); }
			}
					
			document.Load(xml);
		}
	}
}
